package cn.com.doone.common.uc.infrastructure.jdbc;

import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.RandomStringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.com.doone.common.uc.domain.rs.IRegisterJdbcRepository;
import cn.com.doone.common.uc.infrastructure.jdbc.AbstractJdbcRepository;
import cn.com.doone.common.uc.service.dto.UserGroup;
import cn.com.doone.common.uc.service.dto.UserGroupAppAuthorize;
import cn.com.doone.common.uc.service.dto.UserGroupMember;
import cn.com.doone.common.uc.service.dto.UserInfo;
import cn.com.doone.common.uc.utils.DES3;
import cn.com.doone.common.uc.utils.PasswordUtils;
import cn.com.doone.common.uc.utils.PropertiesUtils;
import cn.com.doone.common.uc.utils.StringUtils;

@Repository("registerJdbcRepository")
public class RegisterJdbcRepository extends AbstractJdbcRepository implements IRegisterJdbcRepository {

	@Autowired
	private PropertiesUtils propertiesUtils;
	
	/**
	 * 账号名是否重复
	 */
	@Override
	public boolean validUsername(String userAccount) {
		final String sql = "select count(*) from user_info where STATUS=1 AND (USER_ACCOUNT = ? OR MOBILE_PHONE = ?)";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userAccount, userAccount}, Integer.class);
    	return count == 0 ? false : true;
	}

	/**
	 * 弱密码验证
	 */
	@Override
	public boolean validPassword(String password) {
		final String sql = "select count(*) from weak_password where password = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {password}, Integer.class);
    	return count == 0 ? false : true;
	}
	
	/**
	 * 添加用户
	 */
	@Override
	public int addUser(final UserInfo userInfo) {
		final String sql = "INSERT INTO USER_INFO (USER_ACCOUNT, PASSWORD, ENCRYPTION_TYPE, EMAIL, MOBILE_PHONE, NICKNAME, REALNAME, SEX, STATUS, UPDATE_TIME) VALUE (?,?,?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();
	    jdbcTemplate.update(new PreparedStatementCreator() {
	    	@Override
	        public PreparedStatement createPreparedStatement(java.sql.Connection connection) throws SQLException {
	            PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
	            // 账号
                ps.setString(1, userInfo.getUserAccount());
                // 密码
                try {
					ps.setString(2, PasswordUtils.openLdapMD5(userInfo.getPassword()));
				} catch (NoSuchAlgorithmException e) {
					// TODO Auto-generated catch block
					throw new SQLException();
				}
                // 密码加密版本
                ps.setString(3, "2");
                // 邮件
                ps.setString(4, userInfo.getEmail());
                // 手机号
                ps.setString(5, userInfo.getMobilePhone());
                // 昵称
                ps.setString(6, userInfo.getNickname());
                // 真实姓名
                ps.setString(7, userInfo.getRealname());
                // 性别
                ps.setString(8, userInfo.getSex());
                // 状态
                ps.setString(9, "1");
                
                ps.setTimestamp(10, new java.sql.Timestamp(new java.util.Date().getTime()));
				return ps;
	        }
	    }, keyHolder);
	    return keyHolder.getKey().intValue();
	}
	
	public void removeUser(final String userAccount) {
		final String sql = "UPDATE USER_INFO SET STATUS = '0', UPDATE_TIME = ? WHERE USER_ACCOUNT = ?";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
               
                ps.setTimestamp(1, new java.sql.Timestamp(new java.util.Date().getTime()));
                
                ps.setString(2, userAccount);
            }
        });
	}

	@Override
	public boolean validTenantCode(String tenantCode) {
		final String sql = "select count(*) from user_group where TENANT_CODE = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {tenantCode}, Integer.class);
    	return count == 0 ? false : true;
	}

	@Override
	public int addTenant(final UserGroup userGroup) {
		final String sql = "INSERT INTO USER_GROUP (GROUP_TYPE, GROUP_NAME, DESCRIPTION, TENANT_CODE, STATUS, UPDATE_TIME) VALUE (?,?,?,?,?,?)";
    	KeyHolder keyHolder = new GeneratedKeyHolder();
	    jdbcTemplate.update(new PreparedStatementCreator() {
	    	@Override
	        public PreparedStatement createPreparedStatement(java.sql.Connection connection) throws SQLException{
	            PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
	            // 组类型 “1”为职能组
                ps.setString(1, "1");
                // 租户名
                ps.setString(2, userGroup.getGroupName());
                // 简介
                ps.setString(3, userGroup.getDescription());
                // 租户编号
                ps.setString(4, userGroup.getTenantCode());
                
                ps.setString(5, "1");
                
                // 更新时间
                ps.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime()));
				return ps;
	        }
	    }, keyHolder);
	    return keyHolder.getKey().intValue();
	}
	
	@Override
	public void updateTenant(final UserGroup userGroup) {
		final String sql = "UPDATE USER_GROUP SET GROUP_NAME= ?, DESCRIPTION = ? WHERE TENANT_CODE = ?";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                // 组类型 “1”为租户
                ps.setString(1, userGroup.getGroupName());
                // 租户名
                ps.setString(2, userGroup.getDescription());
                // 租户码
                ps.setString(3, userGroup.getTenantCode());
            }
        });
	}

	@Override
	public void addUserGroupMember(final UserGroupMember userGroupMember) {
		final String sql = "INSERT INTO USER_GROUP_MEMBER (USER_INFO_ID, USER_GROUP_ID, UPDATE_TIME, STATUS) VALUE (?,?,?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, userGroupMember.getUserInfoId());
                ps.setInt(2, userGroupMember.getUserGroupId());
                ps.setTimestamp(3, new java.sql.Timestamp(new java.util.Date().getTime()));
                ps.setString(4, "1");
            }
        });
	}

	@Override
	public int findGroupIdByTenantCode(String tenantCode) {
		final String sql = "SELECT USER_GROUP_ID FROM USER_GROUP WHERE TENANT_CODE = ?";
    	final int userGroupId = jdbcTemplate.queryForObject(sql, new Object[] {tenantCode}, Integer.class);
    	return userGroupId;
	}

	@Override
	public List<Map<String, Object>> findNotSyncUser() {
		final String sql = "SELECT" +
								" userInfo.USER_INFO_ID AS userInfoId," +
								" userInfo.USER_ACCOUNT AS userAccount," +
								" userInfo.`PASSWORD` AS password," +
								" userInfo.EMAIL AS email," +
								" userInfo.MOBILE_PHONE AS mobilePhone," +
								" userInfo.ID_NUMBER AS idNumber," +
								" userInfo.`STATUS` AS status," +
								" userInfo.SEX AS sex," +
								" userInfo.NICKNAME AS nickname," +
								" userInfo.REALNAME AS realname," +
								" userInfo.UPDATE_TIME AS updateTime" +
							" FROM" +
								" user_info userInfo," +
								" ldap_sync ldapSync" +
							" WHERE" +
								" userInfo.UPDATE_TIME > ldapSync.SYNC_TIME" + 
							" AND" +
								" ldapSync.LDAP_SYNC_ID = 1";
    	return jdbcTemplate.queryForList(sql);
	}

	@Override
	public void updateSync() {
		final String sql = "UPDATE " + 
								" LDAP_SYNC" +
							" SET" +
								" SYNC_TIME = ?" +
							" WHERE" +
								" LDAP_SYNC_ID = 1";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setTimestamp(1, new java.sql.Timestamp(new java.util.Date().getTime()));
            }
        });
	}

	@Override
	public String resetPassword(final String userAccount) {
		final String pwd=RandomStringUtils.randomAlphanumeric(8);
		
		final StringBuffer sql = new StringBuffer()
				.append("UPDATE")
				.append(" USER_INFO")
				.append(" SET PASSWORD = ?,")
				.append(" P_CODE = ?,")
				.append(" ENCRYPTION_TYPE = 2")
				.append(" WHERE")
				.append(" USER_ACCOUNT = ?");
		jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					ps.setString(1, PasswordUtils.openLdapMD5(pwd));
					ps.setString(2, DES3.encode(pwd, propertiesUtils.getSecretKey()));
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					throw new SQLException();
				}
				ps.setString(3, userAccount);
			}
		});
		return pwd;
	}
	
	@Override
	public int findAppInfoIdByAppCode(String appCode) {
		final String sql = "SELECT APP_INFO_ID FROM APP_INFO WHERE APP_CODE = ?";
    	final int appInfoId = jdbcTemplate.queryForObject(sql, new Object[] {appCode}, Integer.class);
    	return appInfoId;
	}

	@Override
	public void addGroupAppAuthorize(final UserGroupAppAuthorize userGroupAppAuthorize) {
		final String sql = "INSERT INTO USER_GROUP_APP_AUTHORIZE (USER_GROUP_ID, APP_INFO_ID, AUTH_TIME, EXPIRED_TIME, DEFAULT_AUTH) VALUE (?,?,?,?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, userGroupAppAuthorize.getUserGroupId());
                ps.setInt(2, userGroupAppAuthorize.getAppInfoId());
                ps.setTimestamp(3, new java.sql.Timestamp(new java.util.Date().getTime()));
                ps.setTimestamp(4, new java.sql.Timestamp(new java.util.Date().getTime()));
                ps.setString(5, "1");
            }
        });
	}
	

}
